import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import logging
from logging.handlers import TimedRotatingFileHandler
from datetime import datetime, timedelta
import os
from typing import List, Dict, Any
import urllib.parse  # 新增：用于URL编码

# 配置日志
LOG_DIR = "logs"
os.makedirs(LOG_DIR, exist_ok=True)
log_file = os.path.join(LOG_DIR, "recommend.log")
handler = TimedRotatingFileHandler(
    log_file, when="midnight", interval=1, backupCount=7, encoding="utf-8"
)
formatter = logging.Formatter(
    "%(asctime)s - %(levelname)s - %(message)s", datefmt="%Y-%m-%d %H:%M:%S"
)
handler.setFormatter(formatter)
logger = logging.getLogger("recommend_logger")
logger.setLevel(logging.INFO)
logger.addHandler(handler)
console_handler = logging.StreamHandler()
console_handler.setFormatter(formatter)
logger.addHandler(console_handler)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'WZ123@Hgw',  # 包含@符号的密码
    'database': 'mydb'
}

class DataLoader:
    def __init__(self):
        self.video_df = None
        self.user_df = None
        self.viewlogs_df = None
        self.ks = [0.8, 1.2, 0.2, 1.5, 1, 1.3, 1.4, 1.1, 0.6, 0.7]
        self.engine = None
        self.user_id_field = "user_id"
        
    def create_connection(self):
        """创建数据库连接（处理特殊字符密码）"""
        try:
            # 对密码进行URL编码，处理包含@等特殊字符的情况
            encoded_password = urllib.parse.quote_plus(DB_CONFIG['password'])
            self.engine = create_engine(
                f"mysql+pymysql://{DB_CONFIG['user']}:{encoded_password}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}",
                pool_pre_ping=True
            )
            logger.info("数据库连接成功")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            return False
            
    def check_table_exists(self, table_name):
        """检查表是否存在"""
        try:
            check_query = f"""
            SELECT COUNT(*) as table_exists 
            FROM information_schema.tables 
            WHERE table_schema = '{DB_CONFIG['database']}' 
            AND table_name = '{table_name}'
            """
            result = pd.read_sql(check_query, self.engine)
            exists = result.iloc[0]['table_exists'] > 0
            logger.info(f"表 {table_name} 存在: {exists}")
            return exists
        except Exception as e:
            logger.warning(f"检查表 {table_name} 是否存在时出错: {str(e)}")
            return False

    def load_viewlogs_data(self):
        """加载用户观看历史与性别数据（每次调用均获取最新数据）"""
        try:
            viewlogs_query = "SELECT DISTINCT user_id, user_sex, video_id FROM viewlogs WHERE user_id IS NOT NULL AND video_id IS NOT NULL"
            self.viewlogs_df = pd.read_sql(viewlogs_query, self.engine)
            # 统一用户ID和视频ID格式（字符串）
            self.viewlogs_df["user_id"] = self.viewlogs_df["user_id"].astype(str).str.strip()
            self.viewlogs_df["video_id"] = self.viewlogs_df["video_id"].astype(str).str.strip()
            logger.info(f'成功从viewlogs表加载最新数据: {len(self.viewlogs_df)}条')
            return True
        except Exception as e:
            logger.warning(f"从viewlogs表加载数据失败: {str(e)}")
            self.viewlogs_df = pd.DataFrame()
            return False

    def get_user_watched_videos(self, user_id):
        """获取用户已观看视频列表（去重）"""
        try:
            if self.viewlogs_df is None or self.viewlogs_df.empty:
                logger.warning("viewlogs数据未加载，跳过观看历史过滤")
                return []
                
            user_watched = self.viewlogs_df[
                self.viewlogs_df['user_id'] == str(user_id).strip()
            ]
            watched_videos = user_watched['video_id'].unique().tolist()
            logger.info(f"用户 {user_id} 已观看视频数量: {len(watched_videos)}")
            return watched_videos
        except Exception as e:
            logger.warning(f"获取用户 {user_id} 观看记录失败: {str(e)}，返回空列表")
            return []

    def calculate_time_decay(self, publish_time, base_date=None):
        """计算时间衰减因子（越新视频权重越高）"""
        if base_date is None:
            base_date = datetime.now()
        
        if isinstance(publish_time, str):
            try:
                publish_time = datetime.strptime(publish_time, '%Y-%m-%d %H:%M:%S')
            except:
                try:
                    publish_time = datetime.strptime(publish_time, '%Y-%m-%d')
                except:
                    return 1.0  # 无法解析时间，默认无衰减
        
        days_diff = (base_date - publish_time).days
        decay = np.exp(-days_diff / 30)  # 30天衰减至约0.37
        return max(0.1, min(1.0, decay))  # 衰减范围限制在0.1-1.0

    def load_video_with_labels(self):
        """加载视频与标签数据（关联video_label），排除deleted=1的视频"""
        try:
            # 读取视频基础数据，排除deleted=1的视频
            video_query = "SELECT * FROM video WHERE deleted != 1 OR deleted IS NULL"
            video_df = pd.read_sql(video_query, self.engine)
            if video_df.empty:
                logger.error("video表中无有效数据（所有视频可能已被删除），无法加载视频信息")
                return None
            logger.info(f"从video表加载有效视频数据: {len(video_df)}条（已排除deleted=1的视频）")
            
            # 关联标签数据
            if self.check_table_exists('video_label'):
                label_query = "SELECT video_id, label_name FROM video_label"
                label_df = pd.read_sql(label_query, self.engine)
                if not label_df.empty:
                    logger.info(f"从video_label表加载标签数据: {len(label_df)}条")
                    # 统一字段格式
                    label_df["video_id"] = label_df["video_id"].astype(str).str.strip()
                    label_df["label_name"] = label_df["label_name"].astype(str).str.strip()
                    
                    # 聚合视频标签（一个视频多个标签）
                    label_agg = label_df.groupby("video_id")["label_name"].agg(
                        lambda x: list(set([tag for tag in x if pd.notna(tag) and tag != ""]))
                    ).reset_index()
                    label_agg.columns = ["video_id", "video_tags_list"]
                    
                    # 合并视频与标签数据
                    video_df["video_id"] = video_df["video_id"].astype(str).str.strip()
                    video_df = pd.merge(video_df, label_agg, on="video_id", how="left")
                else:
                    logger.warning("video_label表中无数据，所有视频初始无标签")
                    video_df["video_tags_list"] = [[] for _ in range(len(video_df))]
            else:
                logger.warning("video_label表不存在，所有视频初始无标签")
                video_df["video_tags_list"] = [[] for _ in range(len(video_df))]
            
            # 处理无标签视频（用category兜底）
            if 'category' in video_df.columns:
                video_df["video_tags_list"] = video_df.apply(
                    lambda row: [str(row["category"]).strip()] if (not row["video_tags_list"] and pd.notna(row["category"])) else row["video_tags_list"],
                    axis=1
                )
            video_df["video_tags_list"] = video_df["video_tags_list"].apply(
                lambda x: x if isinstance(x, list) else []
            )
            
            logger.info(f"视频数据加载完成，含标签视频数量: {len(video_df[video_df['video_tags_list'].str.len() > 0])}")
            return video_df
        except Exception as e:
            logger.error(f"加载视频和标签数据失败: {str(e)}")
            return None

    def load_data(self):
        """统一加载所有推荐所需数据"""
        if not self.create_connection():
            return False
            
        try:
            # 1. 加载视频数据
            self.video_df = self.load_video_with_labels()
            if self.video_df is None or self.video_df.empty:
                logger.error("无法加载视频数据，推荐功能不可用")
                return False
            
            # 2. 加载用户画像数据
            if self.check_table_exists('user_portraits'):
                user_query = "SELECT * FROM user_portraits"
                self.user_df = pd.read_sql(user_query, self.engine)
                # 统一用户ID格式
                self.user_df[self.user_id_field] = self.user_df[self.user_id_field].astype(str).str.strip()
                logger.info(f'成功加载用户画像数据: {len(self.user_df)}条，用户列表: {self.user_df[self.user_id_field].unique()[:10]}...')
            else:
                logger.error("user_portraits表不存在，无法进行个性化推荐")
                self.user_df = pd.DataFrame()
                return False
            
            # 3. 初始加载用户观看历史数据（后续推荐时会实时刷新）
            if self.check_table_exists('viewlogs'):
                self.load_viewlogs_data()
            else:
                logger.warning("viewlogs表不存在，无法过滤已观看视频和获取用户性别")
            
            return True
        except Exception as e:
            logger.error(f"从数据库加载数据失败: {str(e)}")
            return False

# 初始化数据加载器
data_loader = DataLoader()

def get_user_recommendations(user_id, top_n=20, temperature=1.5, gender_boost_factor=1.5):
    """
    获取用户个性化推荐视频
    逻辑：1. 实时读取观看记录和视频数据 2. 过滤已观看 3. 解析用户偏好标签 4. 标签匹配评分 5. 时间衰减调整
    """
    logger.info(f"开始为用户 {user_id} 生成个性化推荐，top_n={top_n}")
    user_id_str = str(user_id).strip()
    
    # 每次推荐前强制刷新观看记录和视频数据，确保数据最新
    if data_loader.check_table_exists('viewlogs'):
        data_loader.load_viewlogs_data()
    else:
        logger.warning("viewlogs表不存在，无法更新观看记录")
    
    # 实时刷新视频数据
    if data_loader.engine is not None:
        new_video_df = data_loader.load_video_with_labels()
        if new_video_df is not None:
            data_loader.video_df = new_video_df
        else:
            logger.warning("无法刷新视频数据，将使用缓存数据（如果有）")
    else:
        logger.warning("数据库连接未建立，无法刷新视频数据")
    
    # 1. 过滤用户已观看视频（使用最新的观看记录）
    watched_videos = data_loader.get_user_watched_videos(user_id_str)
    
    # 2. 获取用户性别（用于标签权重调整）
    user_sex = "unknown"
    if data_loader.viewlogs_df is not None and not data_loader.viewlogs_df.empty:
        user_sex_data = data_loader.viewlogs_df[data_loader.viewlogs_df['user_id'] == user_id_str]
        if not user_sex_data.empty and 'user_sex' in user_sex_data.columns:
            sex_value = user_sex_data.iloc[0]["user_sex"]
            if sex_value == 1:
                user_sex = "male"
            elif sex_value == 0:
                user_sex = "female"
        logger.info(f"用户 {user_id_str} 性别: {user_sex}")
    
    # 3. 获取用户画像（无画像则返回热门视频）
    if data_loader.user_df is None or data_loader.user_df.empty:
        logger.warning(f"用户画像数据未加载，为用户 {user_id_str} 返回热门视频")
        return get_hot_videos(watched_videos, top_n)
    
    user_portrait = data_loader.user_df[data_loader.user_df[data_loader.user_id_field] == user_id_str]
    if user_portrait.empty:
        logger.warning(f"用户 {user_id_str} 不存在于画像数据中，返回热门视频")
        return get_hot_videos(watched_videos, top_n)
    
    # 4. 解析用户偏好标签（基于画像计算权重）
    user_prefer_tags = {}
    adjusted_tags_info = {}
    try:
        user_data = user_portrait.iloc[0]
        
        # 4.1 解析标签（逗号分隔格式）
        if "labels" in user_data and pd.notna(user_data["labels"]):
            labels_str = str(user_data["labels"]).strip()
            if labels_str and labels_str != "未知标签":
                labels_list = [tag.strip() for tag in labels_str.split(',') if tag.strip()]
                # 基于播放率计算标签初始权重（播放率越高，标签权重越高）
                base_weight = user_data.get("play_rate", 1.0) * 10
                for tag in labels_list:
                    # 同一标签多次出现则累加权重（基于播放视频数）
                    tag_count = user_data.get("play_videos", 1)
                    user_prefer_tags[tag] = user_prefer_tags.get(tag, 0) + base_weight * tag_count
        
        # 4.2 解析分类（作为补充标签）
        if "category" in user_data and pd.notna(user_data["category"]):
            category_str = str(user_data["category"]).strip()
            if category_str and category_str != "未知分类":
                # 分类权重高于普通标签
                category_weight = user_data.get("play_rate", 1.0) * 15
                user_prefer_tags[category_str] = user_prefer_tags.get(category_str, 0) + category_weight
        
        # 4.3 基于用户行为调整标签权重（点赞、评论、打赏等）
        behavior_weights = {
            "likes": 1.2,    # 点赞加权
            "favorites": 1.5,# 收藏加权
            "reposts": 1.3,  # 转发加权
            "completions": 1.1# 完播加权
        }
        for behavior, weight in behavior_weights.items():
            if behavior in user_data and pd.notna(user_data[behavior]):
                behavior_value = float(user_data[behavior])
                for tag in user_prefer_tags:
                    user_prefer_tags[tag] *= (1 + behavior_value * weight * 0.1)
        
        # 4.4 基于性别调整标签权重
        gender_tag_map = {
            "male": ["游戏", "科技", "体育", "汽车", "军事"],
            "female": ["美妆", "时尚", "母婴", "美食", "情感"]
        }
        if user_sex in gender_tag_map:
            for tag in gender_tag_map[user_sex]:
                if tag in user_prefer_tags:
                    original_weight = user_prefer_tags[tag]
                    user_prefer_tags[tag] *= gender_boost_factor
                    adjusted_tags_info[tag] = f"性别({user_sex})调整: {original_weight:.2f} → {user_prefer_tags[tag]:.2f}"
        
        # 4.5 基于温度参数调整多样性（温度越高，推荐越多样）
        if user_prefer_tags and temperature != 1.0:
            max_score = max(user_prefer_tags.values())
            min_score = min(user_prefer_tags.values())
            for tag in user_prefer_tags:
                # 归一化后应用温度系数
                normalized_score = (user_prefer_tags[tag] - min_score) / (max_score - min_score + 1e-8)
                adjusted_score = normalized_score ** (1/temperature)
                user_prefer_tags[tag] = adjusted_score * (max_score - min_score) + min_score
        
        logger.info(f"用户 {user_id_str} 偏好标签解析完成，共 {len(user_prefer_tags)} 个标签")
        if adjusted_tags_info:
            logger.info(f"标签权重调整记录: {adjusted_tags_info}")
        
    except Exception as e:
        logger.error(f"解析用户 {user_id_str} 偏好标签时出错: {str(e)}", exc_info=True)
        return get_hot_videos(watched_videos, top_n)
    
    # 5. 基于标签匹配计算视频得分
    if data_loader.video_df is None or data_loader.video_df.empty:
        logger.error("视频数据未加载，无法生成推荐")
        return {
            "user_prefer_tags": user_prefer_tags,
            "sorted_prefer_tags": sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10],
            "selected_videos": pd.DataFrame(),
            "user_sex": user_sex,
            "adjusted_tags": adjusted_tags_info,
            "watched_count": len(watched_videos)
        }
    
    # 复制视频数据避免修改原数据
    video_df = data_loader.video_df.copy()
    
    # 5.1 排除已观看视频
    if watched_videos:
        video_df = video_df[~video_df["video_id"].isin(watched_videos)]
        if len(video_df) == 0:
            logger.warning(f"用户 {user_id_str} 已观看所有可用视频，返回空推荐结果")
            return {
                "user_prefer_tags": user_prefer_tags,
                "sorted_prefer_tags": sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10],
                "selected_videos": pd.DataFrame(),
                "user_sex": user_sex,
                "adjusted_tags": adjusted_tags_info,
                "watched_count": len(watched_videos)
            }
    
    # 5.2 计算视频匹配得分（标签匹配+时间衰减）
    def calculate_video_score(row):
        # 标签匹配得分
        tag_score = 0
        video_tags = row.get("video_tags_list", [])
        if isinstance(video_tags, list):
            for tag in video_tags:
                tag = str(tag).strip()
                tag_score += user_prefer_tags.get(tag, 0)
        
        # 时间衰减得分（新视频加分）
        time_decay = 1.0
        if 'publish_time' in row and pd.notna(row["publish_time"]):
            time_decay = data_loader.calculate_time_decay(row["publish_time"])
        
        # 综合得分（标签匹配得分 × 时间衰减）
        return tag_score * time_decay
    
    video_df["match_score"] = video_df.apply(calculate_video_score, axis=1)
    
    # 5.3 处理无匹配标签的视频（若所有视频得分都为0，返回热门视频）
    if video_df["match_score"].sum() == 0:
        logger.info(f"用户 {user_id_str} 无标签匹配的视频，返回热门视频")
        hot_result = get_hot_videos(watched_videos, top_n)
        # 保留用户偏好标签信息
        hot_result["user_prefer_tags"] = user_prefer_tags
        hot_result["sorted_prefer_tags"] = sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10]
        hot_result["user_sex"] = user_sex
        hot_result["adjusted_tags"] = adjusted_tags_info
        return hot_result
    
    # 5.4 按综合得分排序，取top_n
    video_df_sorted = video_df.sort_values(by="match_score", ascending=False).reset_index(drop=True)
    actual_top_n = min(top_n, len(video_df_sorted))
    selected_videos = video_df_sorted.head(actual_top_n)
    
    logger.info(f"用户 {user_id_str} 个性化推荐完成，共推荐 {actual_top_n} 个视频")
    
    return {
        "user_prefer_tags": user_prefer_tags,
        "sorted_prefer_tags": sorted(user_prefer_tags.items(), key=lambda x: x[1], reverse=True)[:10],
        "selected_videos": selected_videos,
        "user_sex": user_sex,
        "adjusted_tags": adjusted_tags_info,
        "watched_count": len(watched_videos)
    }

def get_hot_videos(watched_videos, top_n=20):
    """获取热门视频（按点赞+收藏×1.5+转发×1.3计算热度），排除deleted=1的视频"""
    try:
        # 实时刷新视频数据
        if data_loader.engine is not None:
            new_video_df = data_loader.load_video_with_labels()
            if new_video_df is not None:
                data_loader.video_df = new_video_df
            else:
                logger.warning("无法刷新视频数据，将使用缓存数据（如果有）")
        else:
            logger.warning("数据库连接未建立，无法刷新视频数据")
        
        if data_loader.video_df is None or data_loader.video_df.empty:
            logger.error("视频数据未加载，无法获取热门视频")
            return {
                "user_prefer_tags": {},
                "sorted_prefer_tags": [],
                "selected_videos": pd.DataFrame(),
                "user_sex": "unknown",
                "adjusted_tags": {},
                "watched_count": len(watched_videos)
            }
        
        video_df = data_loader.video_df.copy()
        
        # 排除已观看视频
        if watched_videos:
            video_df = video_df[~video_df["video_id"].isin(watched_videos)]
        if len(video_df) == 0:
            logger.warning("无可用视频推荐（已排除所有已观看视频）")
            return {
                "user_prefer_tags": {},
                "sorted_prefer_tags": [],
                "selected_videos": pd.DataFrame(),
                "user_sex": "unknown",
                "adjusted_tags": {},
                "watched_count": len(watched_videos)
            }
        
        # 计算热度得分（行为加权）
        if all(col in video_df.columns for col in ['likes', 'favorites', 'reposts']):
            video_df['hot_score'] = (
                video_df['likes'].fillna(0) + 
                video_df['favorites'].fillna(0) * 1.5 + 
                video_df['reposts'].fillna(0) * 1.3
            )
            # 时间衰减调整（新视频热度加分）
            if 'publish_time' in video_df.columns:
                video_df['hot_score'] *= video_df['publish_time'].apply(
                    lambda x: data_loader.calculate_time_decay(x) if pd.notna(x) else 1.0
                )
            video_df_sorted = video_df.sort_values(by='hot_score', ascending=False)
        else:
            # 无行为数据时随机推荐
            logger.warning("视频表缺少行为字段（likes/favorites/reposts），随机推荐视频")
            video_df_sorted = video_df.sample(frac=1)
        
        # 取top_n视频
        actual_top_n = min(top_n, len(video_df_sorted))
        selected_videos = video_df_sorted.head(actual_top_n)
        
        logger.info(f"热门视频推荐完成，共推荐 {actual_top_n} 个视频")
        
        return {
            "user_prefer_tags": {},
            "sorted_prefer_tags": [],
            "selected_videos": selected_videos,
            "user_sex": "unknown",
            "adjusted_tags": {},
            "watched_count": len(watched_videos)
        }
    except Exception as e:
        logger.error(f"获取热门视频失败: {str(e)}", exc_info=True)
        return {
            "user_prefer_tags": {},
            "sorted_prefer_tags": [],
            "selected_videos": pd.DataFrame(),
            "user_sex": "unknown",
            "adjusted_tags": {},
            "watched_count": len(watched_videos)
        }

def load_recommendation_data():
    """加载推荐系统所需所有数据（服务启动时调用）"""
    logger.info("开始加载推荐系统数据...")
    if data_loader.load_data():
        logger.info("推荐系统数据加载完成，服务可正常提供个性化推荐")
        # 打印关键数据校验信息
        if not data_loader.user_df.empty:
            logger.info(f"已加载用户数量: {len(data_loader.user_df['user_id'].unique())}")
        if not data_loader.video_df.empty:
            logger.info(f"已加载视频数量: {len(data_loader.video_df)}")
        if not data_loader.viewlogs_df.empty:
            logger.info(f"已加载观看记录数量: {len(data_loader.viewlogs_df)}")
        return True
    else:
        logger.error("推荐系统数据加载失败，个性化推荐功能不可用")
        return False